﻿using CSP.Dao.IObjectDaos.CSP.HB;
using CSP.Model.CSP;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using CSP.Bll.DTO;
using CSP.Bll.DTO.ServiceInfo;
using CSP.Dao.CommonDao;
using CSP.DataAccess;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace CSP.Dao.ObjectDaos.CSP.HB
{
    public class SearchServiceListApp:ISearchServiceListApp
    {
       
        private const string TABLE = "SearchServiceList";

        public SearchServiceListResponseDTO FindBySearchServiceList(SearchServiceListRequestDTO request)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Id,Code,UserName,Title,KeyWords");
            sql.Append(",AgeStart,AgeEnd");
            sql.Append(",CreateTime,QiXianStart,QiXianEnd,EduStart,[EduEnd],[ShenPiTimeStart],[ShenPiTimeEnd],[MonthLiXi],[QianQiCost],[OtherCost]");
            sql.Append(",[Repayment],[PayDescription],[AssetParentId],[AssetChidId],[AssetHouseId],[AssetCarId],[AssetTrustId],[AssetCaiLiaoId] FROM ServiceInfo ");


            string condition = "";
            string separator = "";
            string sort = "";

            #region
            ////服务类型Id
            //if (request.ServiceTypeId.HasValue && false)
            //{
            //    if (request.ServiceLevel == 1)
            //    {
            //        condition += separator + " ServiceTypeIdFirst=" + request.ServiceTypeId.Value;
            //    }
            //    else if (request.ServiceLevel == 2)
            //    {
            //        condition += separator + " ServiceTypeIdSecond=" + request.ServiceTypeId.Value;
            //    }
            //    else if (request.ServiceLevel == 3)
            //    {
            //        condition += separator + " ServiceTypeIdThird=" + request.ServiceTypeId.Value;
            //    }

            //    separator = " and ";
            //}
            #endregion

            //服务地区
            //省份
            sql.Append(" where 1=1");
            //年龄范围
            if (!string.IsNullOrEmpty(request.AgeStart))
            {
                string _requestAgeStart = request.AgeStart;
                string[] splitArray = _requestAgeStart.Split(',');
                int splitArrayLen = splitArray.Length;
                var currentId = string.Empty;

                for (int i = 0; i < splitArrayLen; i++)
                {
                    if (string.IsNullOrEmpty(splitArray[i])) continue;
                    currentId = splitArray[i] + ",";
                    condition += separator + " and (charindex('" + currentId + "',AgeStart) >0) ";

                }

                //condition += separator + " and AgeStart='" + request.AgeStart + ",' ";
            }
            //House
            if (!string.IsNullOrEmpty(request.HouseCase))
            {
                string _requestHouseCase = request.HouseCase;
                string[] splitArray = _requestHouseCase.Split(',');
                int splitArrayLen = splitArray.Length;
                var currentId = string.Empty;

                for (int i = 0; i < splitArrayLen; i++)
                {
                    if (string.IsNullOrEmpty(splitArray[i])) continue;
                    currentId = splitArray[i] + ",";
                    condition += separator + " and (charindex('" + currentId + "',AssetHouseId) >0) ";

                }
                //condition += separator + " and AssetHouseId='" + request.HouseCase + ",' ";
            }

            //Car

            if (!string.IsNullOrEmpty(request.CarCase))
            {
                string _requestCarCase = request.CarCase;
                string[] splitArray = _requestCarCase.Split(',');
                int splitArrayLen = splitArray.Length;
                var currentId = string.Empty;

                for (int i = 0; i < splitArrayLen; i++)
                {
                    if (string.IsNullOrEmpty(splitArray[i])) continue;
                    currentId = splitArray[i] + ",";
                    condition += separator + " and (charindex('" + currentId + "',AssetCarId) >0) ";

                }
                //condition += separator + " and AssetCarId='" + request.CarCase + ",' ";
            }

            //Trust
            if (!string.IsNullOrEmpty(request.TrustCase))
            {
                string _requestTrustCase = request.TrustCase;
                string[] splitArray = _requestTrustCase.Split(',');
                int splitArrayLen = splitArray.Length;
                var currentId = string.Empty;

                for (int i = 0; i < splitArrayLen; i++)
                {
                    if (string.IsNullOrEmpty(splitArray[i])) continue;
                    currentId = splitArray[i] + ",";
                    condition += separator + " and (charindex('" + currentId + "',AssetTrustId) >0) ";

                }
                //condition += separator + " and AssetTrustId='" + request.TrustCase + ",' ";
            }

            if (request.ServiceTypeId != null)
            {
                condition += separator + " and AssetParentId='" + request.ServiceTypeId+"' ";
            }
            if (!string.IsNullOrEmpty(request.ServiceTypeSubId))
            {
                string _requestServiceTypeSub = request.ServiceTypeSubId;
                string[] splitArray = _requestServiceTypeSub.Split(',');
                int splitArrayLen = splitArray.Length;
                var currentId = string.Empty;
                string val = string.Empty;
                string childVal = string.Empty;
                string rootVal = string.Empty;
                for (int i = 0; i < splitArrayLen; i++)
                {
                    if (string.IsNullOrEmpty(splitArray[i])) continue;
                    if (splitArray[i].Split('-').Length > 2)
                    {
                        rootVal = splitArray[i].Split('-')[0];
                        childVal = splitArray[i].Split('-')[1];
                        val = splitArray[i].Split('-')[2];
                    }
                    //currentId = rootVal + "-";
                    condition += separator + " and AssetChidId like '%" + rootVal + "-%'";
                    condition += separator + " and right(substring(substring(AssetChidId,charindex('" + rootVal + "',AssetChidId),10),0,CHARINDEX(',',substring(AssetChidId,charindex('" + rootVal + "',AssetChidId),10))),1) <=" + val;
                    //condition += separator + " and (charindex('" + currentId + "',AssetChidId) >0) ";

                }
            }

            //}

            if (condition != "")
            {
                sql.Append(condition);
            }

            if (sort != "")
            {
                sort = " order by " + sort + "";
            }
            else
            {
                sort = "";
            }
            sql.Append(sort);

            DataSet ds = new DataSet();

            //return result;

            ////实例化Hubble适配器
            //HubbleDataAdapter adapter = new HubbleDataAdapter();

            //adapter.SelectCommand = new HubbleCommand(sql, conn);

            //adapter.SelectCommand.Parameters.Add("@begin", (request.PageIndex ) * request.PageSize);//分页参数-开始索引
            //adapter.SelectCommand.Parameters.Add("@end", (request.PageIndex+1) * request.PageSize-1 );//分页参数-结束索引
            //if (!string.IsNullOrWhiteSpace(request.SearchValue))
            //{ 
            //    adapter.SelectCommand.Parameters.Add("@matchString", matchString);//建立分词
            //}

            SqlConnection conn = SqlHelperFactory.GetBBSConnection();

            try
            {
                ds = SqlHelperFactory.ExecuteDataset(conn, CommandType.Text, sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            //ds = adapter.SelectCommand.Query(request.CacheTimeout);
            SearchServiceListResponseDTO result = new SearchServiceListResponseDTO();

            List<SearchServiceList> searchServiceList = DataTableToSearchServiceList(ds.Tables[0]);
            result.SearchServiceList = searchServiceList;
            result.TotalRecord = ds.Tables[0] != null ? ds.Tables[0].MinimumCapacity : 0;

            return result;
        }
        /// <summary>
        /// 将数据DataTable适配为SearchServiceList
        /// </summary>
        /// <param name="tb"></param>
        /// <returns></returns>
        private List<SearchServiceList> DataTableToSearchServiceList(DataTable tb)
        {
            List<SearchServiceList> result = new List<SearchServiceList>();
            if (tb == null)
            {
                return result;
            }

            for (int i = 0; i < tb.Rows.Count; i++)
            {
                SearchServiceList serviceList = new SearchServiceList();

                serviceList.Id = Convert.ToInt32(tb.Rows[i]["Id"].ToString());
                serviceList.Code = tb.Rows[i]["Code"].ToString();
                serviceList.UserName = tb.Rows[i]["UserName"].ToString();
                serviceList.Title = tb.Rows[i]["Title"].ToString();
                serviceList.KeyWords = tb.Rows[i]["KeyWords"].ToString();
                if (!String.IsNullOrEmpty(tb.Rows[i]["CreateTime"].ToString()))
                { 
                    serviceList.CreateTime =DateTime.Parse(tb.Rows[i]["CreateTime"].ToString());
                }
                serviceList.QiXianStart = Convert.ToInt32(tb.Rows[i]["QiXianStart"].ToString() == "" ? "0" : tb.Rows[i]["QiXianStart"].ToString());
                serviceList.QiXianEnd = Convert.ToInt32(tb.Rows[i]["QiXianEnd"].ToString() == "" ? "0" : tb.Rows[i]["QiXianEnd"].ToString());
                serviceList.EduStart = Convert.ToInt32(tb.Rows[i]["EduStart"].ToString() == "" ? "0" : tb.Rows[i]["EduStart"].ToString());
                serviceList.EduEnd = Convert.ToInt32(tb.Rows[i]["EduEnd"].ToString() == "" ? "0" : tb.Rows[i]["EduEnd"].ToString());
                serviceList.ShenPiTimeStart = Convert.ToInt32(tb.Rows[i]["ShenPiTimeStart"].ToString() == "" ? "0" : tb.Rows[i]["ShenPiTimeStart"].ToString());
                serviceList.ShenPiTimeEnd = Convert.ToInt32(tb.Rows[i]["ShenPiTimeEnd"].ToString() == "" ? "0" : tb.Rows[i]["ShenPiTimeEnd"].ToString());


                serviceList.MonthLiXi = Decimal.Parse(tb.Rows[i]["MonthLiXi"].ToString().Trim() == "" ? "0" : tb.Rows[i]["MonthLiXi"].ToString().Trim());
                serviceList.QianQiCost = tb.Rows[i]["QianQiCost"].ToString();
                serviceList.OtherCost = tb.Rows[i]["OtherCost"].ToString();
                serviceList.Repayment = tb.Rows[i]["Repayment"].ToString();
                serviceList.PayDescription = tb.Rows[i]["PayDescription"].ToString();

                serviceList.AssetParentId = tb.Rows[i]["AssetParentId"].ToString();
                serviceList.AssetChidId = tb.Rows[i]["AssetChidId"].ToString();
                serviceList.AssetHouseId = tb.Rows[i]["AssetHouseId"].ToString();
                serviceList.AssetCarId = tb.Rows[i]["AssetCarId"].ToString();

                serviceList.AssetTrustId = tb.Rows[i]["AssetTrustId"].ToString();
                serviceList.AssetCaiLiaoId = tb.Rows[i]["AssetCaiLiaoId"].ToString();
                result.Add(serviceList);
            }
            return result;
        }
    }
}
